<?php
class TksController extends AppController {

	var $name = 'Tks';
	var $helpers = array('Html','Javascript', 'Ajax');
	var $uses=array('Chiphi','P','Ctrinh','Zphieutien',
		'Quyetdinh','Chudaut','Dvthicong',
		'Nguonvon','Tien','Zchudtnguonvon');
	
	
	/****
	** dap ung cho chuc nang 16 theo hop dong
	thong the co cau kinh phi theo moc thoi gian
	*/
	function tke_thgian(){
		//$kqs=array();
		//$ngaybd=date('Y-m-d');
		//$ngaykt=date('Y-m-d');
		if(!empty($_POST['ngaybd'])){
			$ngaybd=$_POST['ngaybd'];
			$ngaykt=$_POST['ngaykt'];

			$kqs=$this->Zphieutien->find('all',array(
				'conditions'=>array('P.ngaylap between ? and ?'=>array($ngaybd,$ngaykt)),
				'group'=>array('id', 'tenchudautu'),
				'fields'=>array('id', 'tenchudautu','count(*) as soct','sum(tongphi) as phi')));
			$tong=$this->Zphieutien->find('first',array(
				'conditions'=>array('P.ngaylap between ? and ?'=>array($ngaybd,$ngaykt)),
				'fields'=>array('sum(tongphi) as tphi')));
			$tong=$tong[0]['tphi'];
		}
	
		
		$this->set(compact('kqs','tong'));
	}
	function tke_sophieu(){
		if(!empty($_POST['ngaybd'])){
			$ngaybd=$_POST['ngaybd'];
			$ngaykt=$_POST['ngaykt'];
			$this->Zphieutien->recursive=-1;
			$kqs=$this->Zphieutien->find('all',array(
				'conditions'=>array('ngaylap between ? and ?'=>array($ngaybd,$ngaykt)),
				'group'=>array('id', 'tenchudautu'),
				'fields'=>array('id', 'tenchudautu','count(*) as sophieu')));
			$tong=$this->Zphieutien->find('first',array(
				'conditions'=>array('ngaylap between ? and ?'=>array($ngaybd,$ngaykt)),
				'fields'=>array('count(*) as tongphieu')));
			$tong=$tong[0]['tongphieu'];
		}
	
		
		$this->set(compact('kqs','tong'));

	}
	/****
	** dap ung cho chuc nang 14-15 theo hop dong
	*/

	function tke_dvtg_p(){
		
		
		if(!empty($this->data)){
			$this->Ctrinh->recursive=-1;
			$ngaybd=$_POST['ngaybd'];$ngaykt=$_POST['ngaykt'];
			$ct=$this->Ctrinh->find('list',array(
				'conditions'=>array('chudaut_id'=>$this->data ['Tk'] ['chudaut_id']),
				'fields'=>array('id')));

			$this->P->recursive=0;
			$kqs=$this->P->find('all',array(
				'conditions'=>array('P.ctrinh_id'=>$ct,
					'ngaylap between ? and ?'=>array($ngaybd,
						$ngaykt))));
		}
		$chudauts = $this->Ctrinh->Chudaut->find('list',
			array('fields'=>array('id','tenchudautu')));
		$this->set(compact('chudauts','kqs'));
	}
	function tke_nguonvon(){
		if(!empty($_POST['ngaybd'])){
			$ngaybd=$_POST['ngaybd'];
			$ngaykt=$_POST['ngaykt'];
			$this->Zchudtnguonvon->recursive=-1;
			$kqs=$this->Zchudtnguonvon->find('all',array(
				'conditions'=>array('ngaybd between ? and ?'=>array($ngaybd,$ngaykt),
					'id'=>$this->data['Tk'] ['chudaut_id']),
				'group'=>array('id', 'tenchudautu','tennv'),
				'fields'=>array('id', 'tenchudautu','tennv','count(distinct tenctrinh) as soct',
					'sum(sotienvon) as sovon')));
			$tong=$this->Zchudtnguonvon->find('first',array(
				'conditions'=>array('ngaybd between ? and ?'=>array($ngaybd,$ngaykt),
					'id'=>$this->data['Tk'] ['chudaut_id']),
				'fields'=>array('sum(sotienvon) as tongvon')));
			$tong=$tong[0]['tongvon'];
		}
	
		$chudauts = $this->Ctrinh->Chudaut->find('list',
			array('fields'=>array('id','tenchudautu')));
		$this->set(compact('kqs','tong','ngaybd','ngaykt','chudauts'));
	}
	function tk_tenct(){

	}
	function tk_kinhphi(){
		
		if(!empty($this->data)){
			$kqs=$this->Zphieutien->query('
	   			select Zphieutien.ctrinh_id,Ctrinh.tenctrinh,sum(tongphi) as kinhphi 
	   			from Zphieutiens Zphieutien, Ctrinhs Ctrinh
	   			where Zphieutien.ctrinh_id=Ctrinh.id
	   			group by  Zphieutien.ctrinh_id,Ctrinh.tenctrinh
	   			having kinhphi between '.$this->data ['Tk'] ['bd'].' 
	   				and '.$this->data ['Tk'] ['kt']);
		}else{
			$tb='Mời bạn nhập thông tin để tìm';
		}
	   	$this->set(compact('kqs','tb'));

	}
	function tk_thoigian(){
		
		
		
		if(!empty($_POST['ngaybd'])){
			$ngaybd=$_POST['ngaybd'];$ngaykt=$_POST['ngaykt'];
			$this->Zphieutien->recursive=0;
			$kqs=$this->Zphieutien->find('all',array(
				'conditions'=>array('P.ngaylap between ? and ?'=>array($ngaybd,
					$ngaykt))));
		}else{
			$tb='Mời bạn nhập thông tin để tìm';
		}
	   	$this->set(compact('kqs','tb','ngaybd','ngaykt'));

	}
	function tk_nguonvon(){

	}function tk_quyetdinh(){

	}function tk_chudautu(){

	}function tk_tinh(){

	}function tk_dvthicong(){

	}
	//the hien cac cong trinh theo ten-->XONG
	function tim_tenct($q=null) {
               
      //  print_r($ztkbs);
	   $this->layout = 'ajax';
	   $this->P->recursive=0;
	   //loc ra nhung cai da co
	   $this->set('ps', 
       		$this->P->find('all',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('Ctrinh.tenctrinh like '=>'%'.$q.'%'
       					))));
       $this->set(compact('q'));
    }
    
    
    function tim_nguonvon($q=null) {
               
      //  print_r($ztkbs);
	   $this->layout = 'ajax';
	   $this->Nguonvon->recursive=-1;
	   $idnv= $this->Nguonvon->find('list',array('conditions'=>
	   		array('tennv like '=>'%'.$q.'%'),'fields'=>array('id')));
	   
	   $this->Tien->recursive=-1;
	   $idct=$this->Tien->find('list',array('conditions'=>array('nguonvon_id'=>$idnv),
	   		'fields'=>array('ctrinh_id','ctrinh_id')));

	   $this->P->recursive=0;
	   //loc ra nhung cai da co
	   $this->set('ps', 
       		$this->Zphieutien->find('all',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('Zphieutien.ctrinh_id'=>$idct
       					))));

	   $this->Nguonvon->recursive=-1;
		$ttvon=$this->Nguonvon->find('list',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('id'=>$idnv),
       			'fields'=>array('id','tennv')));    
       $this->set(compact('q','ttvon'));

    }
    /** tim theo quyet dinh, XONG*/
    function tim_quyetdinh($q=null) {
               
      //  print_r($ztkbs);
	   $this->layout = 'ajax';
	   $this->Quyetdinh->recursive=-1;
	   $idct= $this->Quyetdinh->find('list',array('conditions'=>
	   		array('noidung like '=>'%'.$q.'%'),'fields'=>array('ctrinh_id')));
	   $idqd= $this->Quyetdinh->find('list',array('conditions'=>
	   		array('noidung like '=>'%'.$q.'%'),'fields'=>array('id')));

	   $this->P->recursive=0;
	   //loc ra nhung cai da co
	   $this->set('ps', 
       		$this->P->find('all',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('P.ctrinh_id'=>$idct
       					))));
	   
	    $this->Quyetdinh->recursive=-1;
		$ttqd=$this->Quyetdinh->find('list',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('ctrinh_id'=>$idqd),
       			'fields'=>array('id','noidung')));    
       
       $this->set(compact('q','ps','ttqd'));

    }
    //XONG
    function tim_chudautu($q=null) {
               
      //  print_r($ztkbs);
	   $this->layout = 'ajax';
	   
	   $this->Chudaut->recursive=-1;
	   
	   $idchu= 
       		$this->Chudaut->find('list',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('tenchudautu like '=>'%'.$q.'%'),
       			'fields'=>array('id')));
//print_r($idchu);	 	  
	   $this->Ctrinh->recursive=-1;
	   
	   $idct= 
       		$this->Ctrinh->find('list',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('chudaut_id'=>$idchu),
       			'fields'=>array('id')));
//print_r($idct);	  
       	 $this->P->recursive=0;
	    $this->set('ps', 
       		$this->P->find('all',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('P.ctrinh_id'=>$idct
       					))));
	    
	    $this->Chudaut->recursive=-1;
		$ttchudautu=$this->Chudaut->find('list',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('id'=>$idchu),
       			'fields'=>array('id','tenchudautu')));    
       $this->set(compact('q','ps','ttchudautu'));
     //  print_r($ps);
    }
    function tim_tinh($q=null) {
               
      //  print_r($ztkbs);
	   $this->layout = 'ajax';
	   $this->P->recursive=0;
	   //loc ra nhung cai da co
	   $this->set('ps', 
       		$this->P->find('all',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('Ctrinh.tenctrinh like '=>'%'.$q.'%'
       					))));
       $this->set(compact('q'));
    }
    //xong
    function tim_dvthicong($q=null) {
               
      //  print_r($ztkbs);
	   $this->layout = 'ajax';
		$this->Dvthicong->recursive=-1;
	   
	   $idthicong= 
       		$this->Dvthicong->find('list',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('tendvthicong like '=>'%'.$q.'%'),
       			'fields'=>array('id')));
//print_r($idchu);	 	  
	   $this->Ctrinh->recursive=-1;
	   
	   $idct= 
       		$this->Ctrinh->find('list',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('dvthicong_id'=>$idthicong),
       			'fields'=>array('id')));
       
	   $this->set('ps', 
       		$this->P->find('all',array(
       			//'fields'=>array('id','tenbb'),
       			'conditions'=>array('P.ctrinh_id'=>$idct
       					))));
	    $this->Dvthicong->recursive=-1;
		$ttthicong=$this->Dvthicong->find('list',array(
       			
       			'conditions'=>array('id'=>$idthicong),
       			'fields'=>array('id','tendvthicong')));    
       $this->set(compact('q','ttthicong'));
    }

	
}
